<%@page trimDirectiveWhitespaces="true"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
 <%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page language="java" import="java.lang.*" %>
<%@ page import = "java.sql.SQLException" %>
<%@ page import = "com.seniorproject.aims.*" %>
<%@ page import = "java.util.List" %>
<%@page import="java.util.ArrayList"%>
<%@ page import = "org.codehaus.jackson.JsonGenerationException" %>
<%@ page import = "org.codehaus.jackson.map.JsonMappingException" %>
<%@ page import = "org.codehaus.jackson.map.ObjectMapper" %>
<%@ page import = "java.io.IOException" %>
<%@ page import = "java.io.File" %>
<%@ page import = "java.io.FileInputStream" %>
<%@ page import = "org.apache.poi.ss.usermodel.Cell" %>
<%@ page import = "org.apache.poi.ss.usermodel.Row" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFSheet" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
<%@ page import = "java.util.Iterator" %>
<%@ page import = "org.apache.poi.hssf.usermodel.HSSFDateUtil" %>
<%@ page import = "java.text.SimpleDateFormat" %>
<%@ page import = "java.util.Date" %>
<%@ page import = "java.util.Properties" %>
<%@ page import = "java.io.InputStream" %>
<%@ page import = "java.sql.PreparedStatement" %>
<%@page import="java.util.regex.Pattern"%>



<%
		
									
		// get session
		String strUser = String.valueOf(session.getAttribute("sUser"));
		HttpSession htp_session = request.getSession();
		
		
		// check session
		if (htp_session == null || htp_session.getAttribute("sUser") == null) {
		    // Forward the control to login.jsp if authentication fails or session expires
		    request.getRequestDispatcher("/login.jsp").forward(request,
		        response);
		}
		
		if("admin".equals(htp_session.getAttribute("sUser"))){%>
		<script>alert("This user dont have PERMISSION to access this zone.");</script>
		<meta HTTP-EQUIV="Refresh" CONTENT="0; URL=admin_home.jsp"> 	
		<%}
		
		
		
		//////////get permission ///////////
		
		String name = String.valueOf(session.getAttribute("name"));
		String surname = String.valueOf(session.getAttribute("surname"));
		String role_name = String.valueOf(session.getAttribute("student_role"));
		
		String department_id = String.valueOf(session.getAttribute("department_id"));
		
		
		System.out.println("name : " + name);///////////////////////////////
		System.out.println("department_id : " + department_id);/////////////////////////
		System.out.println("Detect1");
		
		List<String> system_name = new ArrayList<String>();
		
		Permission permission = new Permission(strUser,name);
		permission.setDepartmentId(department_id);
		permission.setStudentCreate(Boolean.valueOf(String.valueOf(session.getAttribute("student_create"))));
		permission.setStudentDelete(Boolean.valueOf(String.valueOf(session.getAttribute("student_delete"))));
		permission.setStudentRead(Boolean.valueOf(String.valueOf(session.getAttribute("student_read"))));
		permission.setStudentUpdate(Boolean.valueOf(String.valueOf(session.getAttribute("student_update"))));
		
		String permiss = "";
		if(permission.getStudentCreate()) {
			permiss = permiss + "Add";
		}
		if(permission.getStudentUpdate()) {
			if("".equals(permiss)) {
				permiss = permiss + "Edit";
			} else {
				permiss = permiss + " Edit";
			}
		}
		if(permission.getStudentRead()) {
			if("".equals(permiss)) {
				permiss = permiss + "Read";
			} else {
				permiss = permiss + " Read";
			}
		}
		if(permission.getStudentDelete()) {
			if("".equals(permiss)) {
				permiss = permiss + "Delete";
			} else {
				permiss = permiss + " Delete";
			}
		}
		//check permission
		System.out.println("Detect2");
		if(!permission.getStudentRead()){%>
			<script>alert("This user dont have PERMISSION to access this zone.");</script>
			<meta HTTP-EQUIV="Refresh" CONTENT="0; URL=student_home.jsp"> 	
		<%}
		
		/*
		System.out.println("Reset message_path");
		session.setAttribute("message_path", "0");
		System.out.println("message_path:"+session.getAttribute("message_path"));
		*/
		
		//set Database Connection
		String hostProps = "";
		String usernameProps  = "";
		String passwordProps  = "";
		
		try {
			//get current path
			ServletContext servletContext = request.getSession().getServletContext();
			
			InputStream input = servletContext.getResourceAsStream("/properties/connectDB.properties");
			Properties props = new Properties();
			
			props.load(input);
		
			hostProps  = props.getProperty("host");
			usernameProps  = props.getProperty("username");
			passwordProps  = props.getProperty("password");
		} catch (Exception e) { 
			out.println(e);  
		}
		
		System.out.println("hostProperties : "+hostProps);////////////////try/////////////////
		System.out.println("usernameProperties : "+usernameProps);////////////////try/////////////////
		System.out.println("passwordProperties : "+passwordProps);////////////////try/////////////////
		
		// connect database
		Connection connect = null;		
		try {
			Class.forName("com.mysql.jdbc.Driver");
		
			connect =  DriverManager.getConnection("jdbc:mysql://" + hostProps  + "/AIMS" +
					"?user=" + usernameProps  + "&password=" + passwordProps );
		
			if(connect != null){
				System.out.println("Database Connect Sucesses.");
			} else {
				System.out.println("Database Connect Failed.");	
			}
		
		} catch (Exception e) {
			out.println(e.getMessage());
			e.printStackTrace();
		}
	

	System.out.println("@ entollold_import_excel_background");		
	String student_id = request.getParameter("student_id");
	String file_path = request.getParameter("file_path");

	System.out.println("student_id:"+student_id);
	System.out.println("file_path:"+file_path);

	

	String course_id = "";
	String year ="";
	String semester = "";
	String grade = "";
	String enroll_type ="";
	
	String class_id = "";
	
	
	
	int enrollOld_added = 0;
    int i = 0;
    int num_row = 0;
    int num_col  = 0;
	int full_name_added = 0;
	int people_added = 0 ;
	int lecturer_added = 0 ;
	
	List<String> fail_list = new ArrayList<String>();
	List<String> error_log = new ArrayList<String>();
	
	java.util.Date dt = new java.util.Date();

	java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd",java.util.Locale.US);

	String date_now = sdf.format(dt);
	
	int count = 0;
	

		String[] header_array = new  String[]{"รหัสวิชา","ปีการศึกษา","เทอม","เกรด","รูปแบบการลงทะเบียน"};
		
		System.out.println("Header_array size : "+ header_array.length);

	
	try{
		
		FileInputStream file = new FileInputStream(new File(file_path));
		XSSFWorkbook workbook = new XSSFWorkbook(file);
		 
        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        
        Iterator<Row> headerIt_row_check = sheet.iterator();
        Row row_check = headerIt_row_check.next();
        Iterator<Cell> headerIt_cell_check = row_check.cellIterator();

        while (rowIterator.hasNext())
        {	
        	System.out.println(" //////New Row/////  ");
        	System.out.println("Row:"+num_row);
        	if(count==0)
        	{
        		while(headerIt_cell_check.hasNext()){
        			Cell header_cell = headerIt_cell_check.next();
        			   switch (header_cell.getCellType())
                       {
                           case Cell.CELL_TYPE_NUMERIC:
                               break;
                           case Cell.CELL_TYPE_STRING:
                              // System.out.print(header_cell.getStringCellValue()+ " ");
                              	 System.out.println(header_array[i]+":"+header_cell.getStringCellValue());
                               	 if(header_array[i].equals(header_cell.getStringCellValue()))
                               	 {
                               		 System.out.println("Pass:" + i);
                               	 }
                               	 else{
                               		 
                               		throw new Exception();
                               		
                               	 }
                               	 
                               break;
                       }// end switch
        				i++;
        		}// end headerIt_cell_check.hasNext()
        		System.out.println("");
        		count = count +1;
        		rowIterator.next();
        		System.out.println("////////// END Header////////////");
        	}// end if(count==0)
        	
        	  
        	try{	
	            	Row row = rowIterator.next();
					
	                	// rowIterator.next();
	             	
	                	//For each row, iterate through all the columns
	             	   Iterator<Cell> cellIterator = row.cellIterator();
	             	
	                while (cellIterator.hasNext())
	                {
	                	
	                    Cell cell = cellIterator.next();
	                    //Check the cell type and format accordingly
	                   	switch(num_col){
	                   		case 0 :
			      					// title_th
			      					course_id = cell.getStringCellValue();
			      					course_id.replaceAll("\\s+","");
			      					System.out.println("course_id:"+course_id);
	                   			break;
	                   			
	                   			
	                   		case 1 :
	                 			try {
	                 				 int temp_year = Integer.parseInt((cell.getStringCellValue()).replaceAll("\\s+",""));
	                 				 temp_year = temp_year-543;
	                 				 year = String.valueOf(temp_year);
	                 				
	                 			}catch(Exception x){
	                   			
	                   				year = String.valueOf(cell.getNumericCellValue()-543);
	                   				String [] year_temp = year.split(Pattern.quote("."));
	                   				
	                   				year = year_temp[0];
	                   				
	                   				
	                   		
	                 			}
	                 			System.out.println("year:"+year);
	                   			break;
	                   		case 2 :
	                   				try{
	                   					semester = String.valueOf(cell.getStringCellValue()).replaceAll("\\s+","");
	                   					
	                   				}catch(Exception y){
	                   					
	                   				semester = String.valueOf(cell.getNumericCellValue());
	                   				String [] semester_temp = semester.split(Pattern.quote("."));
	                   				semester = semester_temp[0];
	                   				
	                   				}
	                   				System.out.println("semester:"+semester);
	                   				
	                   			break;
	                   		case 3 :
	                   				
	                   				grade  = cell.getStringCellValue();  
	                   				grade.replaceAll("\\s+","");
	                   				System.out.println("grade:"+grade);
	                   			break;
	                   		case 4 :
	                   		
	                   				enroll_type = cell.getStringCellValue();
	                   				enroll_type.replaceAll("\\s+","");
	                   				System.out.println("enroll_type:"+enroll_type);
	                   			break;
	                   	
	                   	}
	                    
	                    num_col++;
	                }// end cellIterator.hasNext();
	                num_col=0;
	                num_row++;
	                
                  			//Start import process
           			System.out.println("Phase1");
			     
                  	try{		
                  		
                  		String sql_cmd = " SELECT *"+
								 " FROM class "+
								 " WHERE course_id='"+course_id+"'"+
								 " AND year='"+year+"'"+
								 " AND semester='"+semester+"'";
                  		System.out.println("sql_cmd:"+sql_cmd);
                  			
					ResultSet rs_class  = connect.createStatement().executeQuery(" SELECT *"+
																				 " FROM class "+
																				 " WHERE course_id='"+course_id+"'"+
																				 " AND year='"+year+"'"+
																				 " AND semester='"+semester+"'");
					
                  	
                  	
	
						if(rs_class.next())
						{
							// Phaese 2 
							class_id = rs_class.getString("class_id");
							
							
							try{
								String sql_enroll = " INSERT INTO `enrollment`(`student_id`, `class_id`, `grade`, `type_of_enrollment`) "+
										" VALUES ('"+student_id+"','"+class_id+"','"+grade+"','"+enroll_type+"')";
								connect.createStatement().executeUpdate(sql_enroll);
								
								/* Log file */
								String log = strUser+" "+name+" "+surname+" import enrollment";
								Log.writeUserFile(log);
								Log.writeUserFile(sql_enroll);
							}catch(Exception y){
								fail_list.add(num_row+1+"");
								System.out.println(y);
								error_log.add(""+num_row+": sql_cmd Phase 2 error");
							}
						
						
							enrollOld_added ++;
							
						}else{
							
							error_log.add(""+num_row+": can't find class_id from database:"+sql_cmd);
							fail_list.add(num_row+1+"");
							
						}
						
                  	
                  	}catch(Exception x){
                  		System.out.println(x);
                  		error_log.add(""+num_row+": sql_cmd Phase 1 error");
                  		fail_list.add(num_row+1+"");
                  		
                  	}
                  	
                  			
                  	
                  		
                   			
	                   			
	               
	                   			
	                   			
	                   			
        	} catch(Exception getData){ //end try Row row = rowIterator.next();
        		System.out.println("Error cant't read Excel:"+getData);
        
        		num_col = 0;
        		fail_list.add(num_row+1+"");
        		num_row++;
        	}
          
       
           	
        }// end while while(rowIterator.hasNext())
        file.close();
        System.out.println("fail_list size :"+fail_list.size());
        for(int num_fail= 0;num_fail<fail_list.size();num_fail++)
        {
        	System.out.println("Fail to import row:"+fail_list.get(num_fail).toString());
        }
        for(int temp_num = 0;temp_num<error_log.size();temp_num++)
        {
        	System.out.println(error_log.get(temp_num).toString());
        }
   
       
        String fail_list_all ="";
        if(fail_list.size()==0)
        {
        	fail_list_all ="";
        }
        else{
        	  fail_list_all = "ข้อมูลผิดพลาดในแถวที่ :";
        	  for(int x=0 ;x<fail_list.size();x++)
              {
        		 
              	fail_list_all = fail_list_all+fail_list.get(x).toString()+" , ";
              }
        	  fail_list_all = fail_list_all + " \n"+ "กรุณาตรวจสอบข้อมูลดังกล่าว ก่อนนำเข้าอีกครั้ง";
        }
      
		out.print("success:\n"
				+enrollOld_added+" to enrollment table  \n"+fail_list_all);
      	  	
		
	}//end try fileinputstream
	catch(Exception x){
		System.out.println(x);
		fail_list.add(num_row+1+"");
		out.print("fail");
		
	}
	
	connect.close();
	
%>